package com.css.fxfzysx.modules.lifeline.repository.impl;

import com.css.fxfzysx.base.dict.service.DictService;
import com.css.fxfzysx.modules.lifeline.entity.basicLibrary.*;
import com.css.fxfzysx.modules.lifeline.param.LifelineFoundationLibraryParam;
import com.css.fxfzysx.modules.lifeline.param.updateBasicLibraryParam;
import com.css.fxfzysx.modules.lifeline.repository.LifelineFoundationLibraryRepository;
import com.css.fxfzysx.modules.lifeline.vo.basic.DestructiveStateVo;
import com.css.fxfzysx.util.PlatformObjectUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
@Slf4j
public class LifelineFoundationLibraryRepositoryImpl implements LifelineFoundationLibraryRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private DictService dictService;

    @Override
    public int getNetworkCountByCondition(BasicLibraryNetworkEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_network where del_flag = '0' and site_classification = '" + entity.getSiteClassification() + "' and material = '" + entity.getMaterial() + "' and joint = '" + entity.getJoint() + "' and year = '" + entity.getYear() + "' and intensity = '" + entity.getIntensity() + "' ");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveUpNetwork(BasicLibraryNetworkEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_network  (id,site_classification,material,joint,diameter,year,intensity,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                entity.getId(), entity.getSiteClassification(), entity.getMaterial(), entity.getJoint(), entity.getDiameter(), entity.getYear(), entity.getIntensity(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getTransformerSubstationCountByCondition(BasicLibrarySubstationEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_substation where del_flag = '0' and type = '" + entity.getType() + "' and year = '" + entity.getYear() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveTransformerSubstation(BasicLibrarySubstationEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_substation  (id,type,year,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getType(), entity.getYear(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getPoolCountByCondition(BasicLibraryPoolEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_pool where del_flag = '0' and pool_shape = '" + entity.getPoolShape() + "' and intensity = '" + entity.getIntensity() + "' and year = '" + entity.getYear() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void savePool(BasicLibraryPoolEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_pool  (id,pool_shape,intensity,year,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getPoolShape(), entity.getIntensity(), entity.getYear(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getRoadCountByCondition(BasicLibraryRoadEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_road where del_flag = '0' and type = '" + entity.getType() + "' and intensity = '" + entity.getIntensity() + "' and year = '" + entity.getYear() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveRoad(BasicLibraryRoadEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_road  (id,type,intensity,year,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getType(), entity.getIntensity(), entity.getYear(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getGasCountByCondition(BasicLibraryGasEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_gas where del_flag = '0' and structural_style = '" + entity.getStructuralStyle() + "' and site_classification = '" + entity.getSiteClassification() + "' and year = '" + entity.getYear() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveGas(BasicLibraryGasEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_gas  (id,structural_style,site_classification,year,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getStructuralStyle(), entity.getSiteClassification(), entity.getYear(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getDevicesCountByCondition(BasicLibraryDevicesEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_devices where del_flag = '0' and floor = '" + entity.getFloor() + "' and installation_situation = '" + entity.getInstallationSituation() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveDevices(BasicLibraryDevicesEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_devices  (id,floor,installation_situation,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getFloor(), entity.getInstallationSituation(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getWireCountByCondition(BasicLibraryWireEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_wire where del_flag = '0' and voltage_level = '" + entity.getVoltageLevel() + "' and site_classification = '" + entity.getSiteClassification() + "' and year = '" + entity.getYear() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveWire(BasicLibraryWireEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_wire  (id,voltage_level,site_classification,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,year,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getVoltageLevel(), entity.getSiteClassification(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getYear(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public int getBridgeCountByCondition(BasicLibraryBridgeEntity entity) {
        StringBuilder sql = new StringBuilder("select count(1) from czt_smx_basic_library_bridge where del_flag = '0' and structural_style = '" + entity.getStructuralStyle() + "' and site_classification = '" + entity.getSiteClassification() + "' and fortification = '" + entity.getFortification() + "' and year = '" + entity.getYear() + "'");
        if (entity.getRange() == 1) {
            sql.append(" and range = " + entity.getRange());
        } else if (entity.getRange() == 2) {
            sql.append(" and range = " + entity.getRange() + " and province = " + entity.getProvince());
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveBridge(BasicLibraryBridgeEntity entity) {
        jdbcTemplate.update("insert into czt_smx_basic_library_bridge  (id,structural_style,site_classification,year,disaster_rate6_state,disaster_rate7_state,disaster_rate8_state,disaster_rate9_state,disaster_rate10_state,create_user,create_user_name,create_time,range,province,province_name,del_flag,fortification,city,city_name,county,county_name ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                entity.getId(), entity.getStructuralStyle(), entity.getSiteClassification(), entity.getYear(), entity.getDisasterRate6State(), entity.getDisasterRate7State(), entity.getDisasterRate8State(), entity.getDisasterRate9State(), entity.getDisasterRate10State(), entity.getCreateUser(), entity.getCreateUserName(), entity.getCreateTime(), entity.getRange(), entity.getProvince(), entity.getProvinceName(), entity.getDelFlag(), entity.getFortification(), entity.getCity(), entity.getCityName(), entity.getCounty(), entity.getCountyName());
    }

    @Override
    public HashMap<String, Object> page(LifelineFoundationLibraryParam param, int page, int size, String provinceId,Integer role) {
        HashMap<String, Object> map = new HashMap<>();
        Integer type = param.getType();
        StringBuilder sql = new StringBuilder("select * from ");
        if (type == 1) {
            sql.append(" czt_smx_basic_library_network");
        } else if (type == 2) {
            sql.append(" czt_smx_basic_library_pool");
        } else if (type == 3) {
            sql.append(" czt_smx_basic_library_gas");
        } else if (type == 4) {
            sql.append(" czt_smx_basic_library_bridge");
        } else if (type == 5) {
            sql.append(" czt_smx_basic_library_road");
        } else if (type == 6) {
            sql.append(" czt_smx_basic_library_substation");
        } else if (type == 7) {
            sql.append(" czt_smx_basic_library_wire");
        } else if (type == 8) {
            sql.append(" czt_smx_basic_library_devices");
        }
        sql.append(" where del_flag = '0'");
        if (PlatformObjectUtils.isNotEmpty(param.getRange())) {
            sql.append(" and range = " + param.getRange());
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql.append(" and province = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
            sql.append(" and city = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
            sql.append(" and county = '" + param.getCounty() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getUserName())) {
            sql.append(" and create_user_name like '%" + param.getUserName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartTime())) {
            sql.append(" and create_time >= '" + param.getStartTime() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getEndTime())) {
            String endTime = param.getEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime + "'");
        }
        sql.append(" order by create_time,update_time desc  limit " + size + " offset " + (page - 1) * size);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Object.class)).size();
        map.put("total", total);

        if (type == 1) {
            List<BasicLibraryNetworkEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryNetworkEntity.class));
            map.put("rows", list);
        } else if (type == 2) {
            List<BasicLibraryPoolEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryPoolEntity.class));
            map.put("rows", list);
        } else if (type == 3) {
            List<BasicLibraryGasEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryGasEntity.class));
            map.put("rows", list);
        } else if (type == 4) {
            List<BasicLibraryBridgeEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryBridgeEntity.class));
            map.put("rows", list);
        } else if (type == 5) {
            List<BasicLibraryRoadEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryRoadEntity.class));
            map.put("rows", list);
        } else if (type == 6) {
            List<BasicLibrarySubstationEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibrarySubstationEntity.class));
            map.put("rows", list);
        } else if (type == 7) {
            List<BasicLibraryWireEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryWireEntity.class));
            map.put("rows", list);
        } else if (type == 8) {
            List<BasicLibraryDevicesEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BasicLibraryDevicesEntity.class));
            map.put("rows", list);
        }
        return map;
    }

    @Override
    public void updateById(updateBasicLibraryParam param) {
        Integer type = param.getType();
        StringBuilder sql = new StringBuilder("update ");
        if (type == 1) {
            sql.append(" czt_smx_basic_library_network");
        } else if (type == 2) {
            sql.append(" czt_smx_basic_library_pool");
        } else if (type == 3) {
            sql.append(" czt_smx_basic_library_gas");
        } else if (type == 4) {
            sql.append(" czt_smx_basic_library_bridge");
        } else if (type == 5) {
            sql.append(" czt_smx_basic_library_road");
        } else if (type == 6) {
            sql.append(" czt_smx_basic_library_substation");
        } else if (type == 7) {
            sql.append(" czt_smx_basic_library_wire");
        } else if (type == 8) {
            sql.append(" czt_smx_basic_library_devices");
        }
        sql.append(" set disaster_rate6_state=?,disaster_rate7_state=?,disaster_rate8_state=?,disaster_rate9_state=?,disaster_rate10_state=? where id = ?");
        jdbcTemplate.update(sql.toString(), param.getDisasterRate6State(), param.getDisasterRate7State(), param.getDisasterRate8State(), param.getDisasterRate9State(), param.getDisasterRate10State(), param.getId());
    }

    @Override
    public void deleteByTypeAndIds(Integer type, String id) {
        StringBuilder sql = new StringBuilder("update ");
        if (type == 1) {
            sql.append(" czt_smx_basic_library_network");
        } else if (type == 2) {
            sql.append(" czt_smx_basic_library_pool");
        } else if (type == 3) {
            sql.append(" czt_smx_basic_library_gas");
        } else if (type == 4) {
            sql.append(" czt_smx_basic_library_bridge");
        } else if (type == 5) {
            sql.append(" czt_smx_basic_library_road");
        } else if (type == 6) {
            sql.append(" czt_smx_basic_library_substation");
        } else if (type == 7) {
            sql.append(" czt_smx_basic_library_wire");
        } else if (type == 8) {
            sql.append(" czt_smx_basic_library_devices");
        }
        sql.append(" set del_flag = '1' where id = '" + id + "'");
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public DestructiveStateVo getByBasicInfo(Map<String, Object> map, Integer type, Integer range) {
        DestructiveStateVo destructiveStateVo = new DestructiveStateVo();
        try {
            if (type == 1 || type == 15) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_network where del_flag = '0' and site_classification = '" + map.get("site_classification") + "' and material = '" + map.get("material") + "' and joint = '" + map.get("joint") + "' and diameter = '" + map.get("diameter") + "' and year = '" + map.get("year") + "' and intensity = '" + map.get("intensity") + "' and range = " + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_network where del_flag = '0' and site_classification = '" + map.get("site_classification") + "' and material = '" + map.get("material") + "' and joint = '" + map.get("joint") + "' and diameter = '" + map.get("diameter") + "' and year = '" + map.get("year") + "' and intensity = '" + map.get("intensity") + "' and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "' and range = " + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 2) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_pool where del_flag = '0' and pool_shape = '" + map.get("shape") + "' and intensity = '" + map.get("intensity") + "' and year = '" + map.get("year") + "' and range = " + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_pool where del_flag = '0' and pool_shape = '" + map.get("shape") + "' and intensity = '" + map.get("intensity") + "' and year = '" + map.get("year") + "' and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "' and range = " + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 3) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_gas where del_flag = '0' and structural_style = '" + map.get("structural_style") + "' and site_classification = '" + map.get("site") + "' and year = '" + map.get("year") + "' and range = " + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_gas where del_flag = '0' and structural_style = '" + map.get("structural_style") + "' and site_classification = '" + map.get("site") + "' and year = '" + map.get("year") + "' and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "' and range = " + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 4) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_bridge where del_flag = '0' and structural_style = '" + map.get("super_structure") + "' and site_classification = '" + map.get("site_soil") + "' and year = '" + map.get("year") + "' and fortification = '" + map.get("fortification") + "' and range =" + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_bridge where del_flag = '0' and structural_style = '" + map.get("super_structure") + "' and site_classification = '" + map.get("site_soil") + "' and year = '" + map.get("year") + "' and fortification = '" + map.get("fortification") + "' and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city_") + "' and county_name = '" + map.get("county") + "' and range =" + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 5) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_road where del_flag = '0' and type = '" + map.get("site_soil") + "' and intensity = '" + map.get("intensity") + "' and year = '" + map.get("year") + "' and range =" + range);
                if(range == 1){
                    sql.append(" and province_name= '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_road where del_flag = '0' and type = '" + map.get("site_soil") + "' and intensity = '" + map.get("intensity") + "' and year = '" + map.get("year") + "' and province_name= '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "' and range =" + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 6) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_substation where del_flag = '0' and year = '" + map.get("year") + "' and type = '"+map.get("type")+"' and range = " + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_substation where del_flag = '0' and year = '" + map.get("year") + "' and province_name = '" + map.get("province") + "' and type = '"+map.get("type")+"' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "' and range = " + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 8) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_wire where del_flag = '0' and voltage_level = '" + getKeyByValue("smxdlxldydj",map.get("level").toString()) + "' and site_classification = '" + map.get("site") + "' and range = " + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_wire where del_flag = '0' and voltage_level = '" + map.get("voltage_level") + "' and site_classification = '" + map.get("site_classification") + " and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name = '" + map.get("county") + "' and range = '" + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            } else if (type == 9) {
                StringBuilder sql = new StringBuilder("select * from czt_smx_basic_library_devices where floor = '" + map.get("in_floor") + "' and range = " + range);
                if(range == 1){
                    sql.append(" and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name= '" + map.get("county") + "'");
                }
//                String sql = "select * from czt_smx_basic_library_devices where floor = '" + map.get("in_floor") + " and province_name = '" + map.get("province") + "' and city_name = '" + map.get("city") + "' and county_name= '" + map.get("county") + " and range = " + range;
                destructiveStateVo = jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(DestructiveStateVo.class));
            }
        } catch (DataAccessException e) {
            destructiveStateVo = new DestructiveStateVo();
        }
        return destructiveStateVo;
    }

    private String getKeyByValue(String code, String value) {
        if (!PlatformObjectUtils.isEmpty(value)) {
            String key = dictService.getKeyByDictCodeAndValue(code, value.trim());
            return key;
        }
        return null;
    }
}
